package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Degree;

import lombok.extern.slf4j.Slf4j;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

@Slf4j
public final class DegreeDao {
    private static DegreeDao degreeDao = new DegreeDao();

    private DegreeDao() {
    }

    public static DegreeDao getInstance() {
        if (degreeDao == null) {
            degreeDao = new DegreeDao();
        }
        return degreeDao;
    }

    public static Collection<Degree> findAll(Connection connection, List conditions_json, Pagination pagination) throws SQLException {
        Collection<Degree> desiredDegrees = new HashSet<Degree>();
        int totalNum = CommonDao.count(connection, "Degree");
        StringBuilder sql_select = new StringBuilder("SELECT*FROM Degree");
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            sql_select.append(pagination.toLimitClause(totalNum));
        }
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {

            //根据id查找结果创建Degree对象
            Degree degree = new Degree(
                    resultSet_select.getInt("id"),
                    resultSet_select.getString("description"),
                    resultSet_select.getString("no"),
                    resultSet_select.getString("remarks"));
            desiredDegrees.add(degree);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        log.debug("" + desiredDegrees);
        return desiredDegrees;
    }


    //根据id，查找学院
    public Degree find(Integer id, Connection connection) throws SQLException {

        String deleteDegree_sql = "select * from Degree where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建degree对象
        Degree degree = new Degree(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭
        JdbcHelper.close(pstmt, connection);
        return degree;
    }

    //修改
    public boolean update(Degree degree, Connection connection) throws SQLException {

        String deleteDegree_sql = "" +
                "UPDATE Degree SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setString(1, degree.getNo());
        pstmt.setString(2, degree.getDescription());
        pstmt.setString(3, degree.getRemarks());
        pstmt.setInt(4, degree.getId());
        return pstmt.executeUpdate() > 0;
    }

    //添加学院
    public boolean add(Degree degree, Connection connection) throws SQLException {

        String deleteDegree_sql = "" +
                "INSERT INTO degree(no,description,remarks) VALUES" + "(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setString(1, degree.getNo());
        pstmt.setString(2, degree.getDescription());
        pstmt.setString(3, degree.getRemarks());
        //添加学院 并判断是否添加成功
        return pstmt.executeUpdate() > 0;
    }

    //根据Degree对象删除
    public static boolean delete(Degree degree, Connection connection) throws SQLException {

        String deleteDegree_sql = "delete from Degree where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteDegree_sql);
        //为预编译参数赋值
        pstmt.setString(1, degree.getNo());
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate() > 0;
    }

}


